package ServerOperation;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;



import Controllers.GenericToolsForController.PDFfile;
import Controllers.GenericToolsForController.RequestMainStatusEnum;
import Controllers.GenericToolsForController.RequestStepStatusEnum;
import Controllers.GenericToolsForController.WorkerPostion;
import Entity.InfoSystem;
import Entity.Message;
import Entity.Report;
import Entity.Request;
import Entity.RequestStatusHistory;
import Entity.Status;
import Entity.User;
import Entity.Worker;

public class GenricOperation 
{
	
	
	/**
	 * @author Shimon Arzuan
	 * return the next is in the table "table"
	 * @param table
	 * @param conn
	 * @return
	 */
	public static int getNextId(String table,Connection conn)
	{
		int returnId=0;
		String SpecficQuery = new String("SHOW TABLE STATUS LIKE '"+table+"'");
		//System.out.println(SpecficQuery);
		//System.out.println("getNextId");
		
		try 
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SpecficQuery);
			
			if(rs.next())
			{
				//System.out.println("ID = " + rs.getInt("Auto_increment"));
				//System.out.println("ID = " + rs.getString("Auto_increment"));
				returnId = rs.getInt("Auto_increment");
			}
			stmt.close();
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}

		return returnId;	
	}
	
	
	/**
	 * @author Shimon Arzuan
	 * write on the server new file, get a PDFfile that contain a byte array.
	 * @param user
	 * @param filename
	 * @param file
	 * @return
	 */
	public static String createFileInServer(User user,String filename, PDFfile file)
	{
		
		String currentDir = System.getProperty("user.dir");
		String path = currentDir+"\\Files";
		File dir=new File(path);
		if(! dir.exists())
		{
			dir.mkdir();
			System.out.println("Creats new dir : "+path);
		}
		
		path = dir.getPath()+ "\\" + user.getUsername();
		
		dir=new File(path);
		if(! dir.exists())
		{
			dir.mkdir();
			System.out.println("Creats new dir : "+path);
		}
		System.out.println(path);
		
		String FileName =  path + "\\" + filename + ".pdf";
		
		System.out.println(FileName);
		
		File newfile = new File(FileName);
		
		try {
			FileOutputStream fos = new FileOutputStream(newfile);
			fos.write(file.input);
			fos.close();
		} 
		catch (IOException e)
		{
			e.printStackTrace();
		}
		return newfile.getPath().replace("\\" , "/");
	}
	
	/**
	 * @author Shimon Arzuan
	 * return Instance of Status that heave the "statusId", if there is not
	 * such row that math to this id return null 
	 * @param statusId
	 * @param conn
	 * @return
	 */
	public static Status getStatusById(int statusId,Connection conn)
	{
		String SpecficQuery = new String("SELECT * FROM status WHERE id = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, statusId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
				String main = rs.getString("MainStatus");
				String step = rs.getString("StepStatus");
				stmt.close();
				return new Status(statusId,main,step);
			}
			stmt.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;	
	}
	/**
	 * @author Shimon Arzuan
	 * return the ID of Status that heave the fit whit the status, if there is not
	 * such row that math to this id return 0 
	 * @param status
	 * @param conn
	 * @return
	 */
	public static int getIdStatus(Status status,Connection conn)
	{
		String SpecficQuery = new String("SELECT * " +
										"FROM status " +
										"WHERE MainStatus = ? AND StepStatus = ?");
		System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, status.getmStatus().getText());
			stmt.setString(2, status.getsStatus().getText());
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
				//stmt.close();
				return rs.getInt("id");
			}
			stmt.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return 0;	
	}
	
	
	
	/**
	 * return new Instance of User by id
	 * @param userId
	 * @param conn
	 * @return
	 */
	public static User getUserById(int userId,Connection conn)
	{
		String SpecficQuery = new String("SELECT * FROM user WHERE id = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, userId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
			 String firstName = rs.getString("fname");
			 String lastName = rs.getString("lname");
			 String email = rs.getString("email");
			 String phone = rs.getString("phone");
			 String username = rs.getString("username");
			 String descriptionType = rs.getString("descriptionType");
			 stmt.close();
			return new User(userId, firstName, lastName, email, phone, username, descriptionType);
			}
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;	
	}
	
	
	/**
	 * return new Instance of Worker by id
	 * @param workerId
	 * @param conn
	 * @return
	 */
	
	public static Worker getWorkerById(int workerId,Connection conn)
	{
		String SpecficQuery = new String("SELECT * FROM worker WHERE id = ?" );
	//	System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, workerId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
				int userId =  rs.getInt("userid");
				String role = rs.getString("Workertype");
				User user = getUserById(userId,conn);
				stmt.close();
				return new Worker(workerId, role, user);
			}
			stmt.close();
			
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;	
	}
	
	
	/**
	 * return new Instance of RequestStatusHistory by historyId, in Data base RequestStatusHistory implements
	 * by two tables: History and  RequestHistory.
	 * @param historyId
	 * @param conn
	 * @return
	 */
	public static RequestStatusHistory getHistoryById(int historyId,Connection conn)
	{
		String SpecficQuery = new String("SELECT * FROM history WHERE id = ?" );
	//	System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, historyId);
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{	 
				Status status = getStatusById(rs.getInt("Status"),conn);
				 Date startDate = rs.getDate("StartDate");
				 Date dueDate = rs.getDate("DueDate");
				 Date finishDate = rs.getDate("EndDate");
				 String comment = rs.getString("Comment");
				 
				 int nextHistory = rs.getInt("nextHistory"); 
				 int prevHistory = rs.getInt("prevHistory");
				 
				 int reportID = rs.getInt("Details");
				 Report details = (reportID==0)? null : getReportById(reportID, conn);
				 stmt.close();
				 return new RequestStatusHistory(historyId,status, startDate, dueDate, finishDate, comment, details,prevHistory,nextHistory);
			}
			stmt.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;	
	}
	
	
	/**
	 * author : guy peleg
	 * this function returns info system by id
	 * @param infoid
	 * @param conn
	 * @return info system
	 */
	public static InfoSystem getInfoSystem(int infoid,Connection conn){
		String SpecficQuery = new String("SELECT * FROM info_system WHERE id = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, infoid);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next()){
				InfoSystem info = new InfoSystem(rs.getString("name"),rs.getString("description"),GenricOperation.getWorkerById(rs.getInt("supervisor"), conn));
				stmt.close();
				return info;
			}
			stmt.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * return Instance of Report by id
	 * @param reportId
	 * @param conn
	 * @return
	 */
	public static Report getReportById(int reportId,Connection conn){
		String SpecficQuery = new String("SELECT * FROM report WHERE id = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, reportId);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next())
			{
				Worker creator = getWorkerById(rs.getInt("Creator"), conn);
				String comment = rs.getString("comment");
				String filePathInServer = rs.getString("File");
				PDFfile pdfFile =(filePathInServer==null)?null:new PDFfile(new File(filePathInServer));
				stmt.close();
				return new Report(creator, reportId, null, comment, pdfFile);
			}
			stmt.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}
	
	
	
	public static Request getRequestById(int requestId,Connection conn){
		String SpecficQuery = new String("SELECT * FROM request WHERE id = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setInt(1, requestId);
			ResultSet rs = stmt.executeQuery();
			
			if (rs.next())
			{
				Request req = new Request();
				
				req.setRequestId(requestId);
				
				String description = rs.getString("Description");
				req.setDescription(description);
				
				Date dayOfCreation =rs.getDate("dayOfCreation");
				req.setDayOfCreation(dayOfCreation);
				
				String notes = rs.getString("Note");
				req.setNotes(notes);
				
				String filePathInServer = rs.getString("File");
				PDFfile pdfFile =(filePathInServer==null)?null:new PDFfile(new File(filePathInServer));
				req.setFile(pdfFile);
				
				String currentState = rs.getString("currentState");
				req.setCurrentState(currentState);
				
				String propsedChange = description;
				req.setProposedChange(propsedChange);
				
				String execuse = rs.getString("excuse");
				req.setExecuse(execuse);
				
				
				 int timeForEvaluation = rs.getInt("timeForEvluation");
				 req.setTimeForEvaluation(timeForEvaluation);
				 
				 int timetimeForExecution = rs.getInt("timeForExecution");
				 req.setTimetimeForExecution(timetimeForExecution);
				 
				 int timeForReview = rs.getInt("timeForReview");
				 req.setTimeForReview(timeForReview);
				
				 int timeForTesting = rs.getInt("timeForTesting");
				 req.setTimeForTesting(timeForTesting);
				 
				
				InfoSystem infoSystem = getInfoSystem(rs.getInt("infoSystem"), conn);
				req.setInfoSystem(infoSystem);
				
				RequestStatusHistory currentStatus = getHistoryById(rs.getInt("currentStep"), conn);
				req.setCurrentStatus(currentStatus);
				
				Worker inspector = getWorkerById(rs.getInt("inspector"), conn);
				req.setInspector(inspector);
				
				Worker evaluator = getWorkerById(rs.getInt("evlutaor"), conn);
				req.setEvaluator(evaluator);
				
				Worker executionLeader = getWorkerById(rs.getInt("executionLeader"), conn);
				req.setExecutionLeader(executionLeader);
				
			    Worker cometteMembers1 = getWorkerById(rs.getInt("CommitieMember1"), conn);
				req.setCometteMembers1(cometteMembers1);
				
				Worker cometteMembers2 = getWorkerById(rs.getInt("CommitieMember2"), conn);
				req.setCometteMembers2(cometteMembers2);
				
				Worker chairman = getWorkerById(rs.getInt("ChairMan"), conn);
				req.setChairman(chairman);
				
				Worker examiner = getWorkerById(rs.getInt("examiner"), conn);
				req.setExaminer(examiner);
				
				User initiator = getUserById(rs.getInt("initiator"), conn);
				req.setInitiator(initiator);
				
				 Report testFailureReport = getReportById(rs.getInt("testFailureReport"), conn);
				 req.setTestFailureReport(testFailureReport);
				 
				Report evaluationReport = getReportById(rs.getInt("evluationReport"), conn);
				req.setEvaluationReport(evaluationReport);
				
				List<RequestStatusHistory> history = getAllRequestHistory(requestId,conn);
				req.setHistory(history);
				stmt.close();
				return req;
			}
			stmt.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public static List<Request> getAllRequestByStatus(Status status,Connection conn)
	{
		String SpecficQuery = new String("Select * " +
										 "from request, history ,Status  "+
										 "where request.currentStep = history.id AND history.Status = Status.id AND Status.MainStatus = ? AND Status.StepStatus = ?" );	
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, status.getmStatus().getText());
			stmt.setString(2, status.getsStatus().getText());
			ResultSet rs = stmt.executeQuery();
			
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{			
				Request var = getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}	
			stmt.close();
			return requestlist;
		}
		catch (SQLException e) 
		{
		e.printStackTrace();
		}
		
		return null;
	}
	
	public static List<Request> getAllRequestByStatus(Status status,int chairManId, Connection conn)
	{
		String SpecficQuery = new String("Select * " +
										 "from request, history ,Status  "+
										 "where request.currentStep = history.id AND history.Status = Status.id AND Status.MainStatus = ? AND Status.StepStatus = ? AND request.ChairMan = ?" );	
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, status.getmStatus().getText());
			stmt.setString(2, status.getsStatus().getText());
			stmt.setInt(3, chairManId);
			ResultSet rs = stmt.executeQuery();
			
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{			
				Request var = getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}	
			stmt.close();
			return requestlist;
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}

		return null;
	}


	public static List<RequestStatusHistory> getAllRequestHistory(int requestId,Connection conn)
	{
		List<RequestStatusHistory> list = null;
		String SpecficQuery = new String("Select * " +
				"from request, history ,requesthistory  "+
				 "where request.id = requesthistory.reqID AND history.id = requesthistory.historyID AND request.id = ?" );	
			try 
			{
				PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
				stmt.setInt(1,requestId);
				ResultSet rs = stmt.executeQuery();
			//	System.out.println(rs.toString());
				list = new ArrayList<RequestStatusHistory>();
				while (rs.next())
				{			
					list.add(getHistoryById(rs.getInt("history.id"),conn));
				}	
				stmt.close();
			}
			catch (SQLException e) 
			{
				e.printStackTrace();
			}		
		return list;
	}




	public static List<Request> getAllRequestByMainStatus(String mstatus,Connection conn)
	{
		String SpecficQuery = new String("Select * " +
						 "from request, history ,Status  "+
						 "where request.currentStep = history.id AND history.Status = Status.id AND Status.MainStatus = ?" );
	//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, mstatus);
			
			ResultSet rs = stmt.executeQuery();
			
			//System.out.println(rs.toString());
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{
				Request var = GenricOperation.getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}	
			stmt.close();
			return requestlist; 
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return null;
	}

	public static List<Request> getAllRequestByMainStatusAndInspector(String mstatus,int inspectorId,Connection conn)
	{
		String SpecficQuery = new String("Select * " +
						 "from request, history ,Status  "+
						 "where request.currentStep = history.id AND " +
						 "history.Status = Status.id AND Status.MainStatus = ? AND request.inspector = ?" );
		//System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, mstatus);
			stmt.setInt(2, inspectorId);
			ResultSet rs = stmt.executeQuery();
			
		//	System.out.println(rs.toString());
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{
				Request var = GenricOperation.getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}
			stmt.close();
			return requestlist; 
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return null;
	}

	
	public static List<Request> getAllRequestByStatusAndWorkerPosition(Status[] statusArray,int workerId,WorkerPostion position,Connection conn)
	{
		
		if(statusArray.length<0)
		{
			System.out.println(" Satus array in getAllRequestByStatusAndWorkerPosition is empty");
			return null;
		}
		
		String MainStatus=new String();
		String StepStatus=new String(" (");
		//System.out.println(statusArray.length);
		for(int i=0 ; i<statusArray.length ; i++)
		{
			
			if(i==statusArray.length-1)
			{
				StepStatus +=  " (Status.StepStatus = " + statusArray[i].getsStatus().getText() + " AND status.MainStatus = " + statusArray[i].getmStatus().getText()+" )) AND " ;
			}
			else
				StepStatus +=  "( Status.StepStatus = " + statusArray[i].getsStatus().getText() + " AND status.MainStatus = " + statusArray[i].getmStatus().getText() + " ) OR ";
		}
	//	MainStatus += "AND Status.MainStatus = " + statusArray[0].getmStatus().getText() + " AND ";
		
		
		String SpecficQuery = new String("Select request.id " +
									"from request, history ,Status  "+
									"where " + StepStatus + " request.currentStep = history.id " +
									"AND history.Status = Status.id " +
									"AND request."+ position.getText() +" = "+ workerId +" ; ");
									
		System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
		//	stmt.setInt(1, workerId);
			ResultSet rs = stmt.executeQuery();
			
			//System.out.println(rs.toString());
			
			List<Request> requestlist = new ArrayList<Request>();
			while (rs.next())
			{
				Request var = GenricOperation.getRequestById(rs.getInt("request.id"), conn);
				requestlist.add(var);
			}	
			stmt.close();
			return requestlist; 
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return null;
	}

	
	
	public static boolean updateVariableInTable(String TabelName,String rowId,String varame,String value,Connection conn)
	{
		String SpecficQuery = new String("Update "+TabelName
				                         +" set "+varame+" = "+value+ 
				                         " WHERE id = "+rowId);
		try 
		{
			System.out.println(SpecficQuery);
			 Statement stmt = conn.createStatement();
			stmt.executeUpdate(SpecficQuery);
			stmt.close();
			return true;
		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;	
	}


	public static List<Worker> getAllCommitterMemeberForRequest(int reqID,Connection conn)
	{
		Request req = getRequestById(reqID,conn);
		List<Worker> lst = new ArrayList<Worker>();
		if (req!=null){
			lst.add(req.getChairman());
			lst.add(req.getCometteMembers1());
			lst.add(req.getCometteMembers2());
			return lst;
		}
		return null;
	}
	
	public static Worker getIcmManager(Connection conn)
	{
		
		String SpecficQuery = new String("SELECT * "+
				" FROM user  , worker ,workerroll "+ 
				" WHERE user.id = worker.userid AND workerroll.id = ?");
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, "1");
			ResultSet rs = stmt.executeQuery();

			if (rs.next())
			{
				return getWorkerById(rs.getInt("worker.id"), conn);
			}
			stmt.close();
		}
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;

	}
	
}
